# Copyright (C) 2007 Denis Crowdy, Macquarie University, Australia
# All rights reserved.
#
# This software is licensed as described in the file COPYING, which
# you should have received as part of this distribution.
#
# This software consists of voluntary contributions made by many
# individuals. For exact contribution history, see the revision
# history and logs.
#
# Author: Denis Crowdy <Denis.Crowdy@mq.edu.au>
import string
from pylons import config
from sqlalchemy import Column, MetaData, Table, ForeignKey, types
from sqlalchemy.sql import and_, or_, not_
from sqlalchemy.orm import mapper, relation
from sqlalchemy.orm import scoped_session, sessionmaker
#from sacontext import PylonsSAContext
import arkaiv.lib.helpers as h
from operator import itemgetter
#from arkaiv.model.oggzinfo import OggzInfo

#sac = PylonsSAContext()
#sac.add_engine_from_config("default")
Session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=config['pylons.g'].sa_engine))

metadata = MetaData()

system_table = Table('system', metadata,
        Column('db_version', types.Integer)
)

collections_table = Table('collections', metadata,
	Column('ixk', types.Integer, primary_key=True),
	Column('name', types.String()),
        Column('role', types.String()),
        Column('access', types.String())
)

items_table = Table('items', metadata,
	Column('ixi', types.Integer, primary_key=True),
	Column('name', types.String()),
	Column('cmmlfile', types.String())
)

collection_items_table = Table('collection_items', metadata,
	Column('ixci', types.Integer, primary_key=True),
	Column('ixk', types.Integer, ForeignKey('collections.ixk')),
	Column('ixi', types.Integer, ForeignKey('items.ixi'))
)

#authorisation_table = Table('authorisation', metadata,
#        Column('ixa', types.Integer, primary_key=True),
#        Column('ixk', types.Integer, ForeignKey('collections.ixk')),
#        Column('role', types.String())
#        Column('access', types.String())
#)

playlists_table = Table('playlists', metadata,
    Column('ixp', types.Integer, primary_key=True),
    Column('name', types.String())
)

playlist_items_table = Table('playlist_items', metadata,
    Column('ixpi', types.Integer, primary_key=True),
    Column('ixp', types.Integer(), ForeignKey('playlists.ixp')),
    Column('ixi', types.Integer(), ForeignKey('items.ixi')),
    Column('ixc', types.Integer(), ForeignKey('clips.ixc'))
)

sources_table = Table('sources', metadata,
	Column('ixs', types.Integer, primary_key=True),
	Column('path', types.String())
)

item_sources_table = Table('item_sources', metadata,
	Column('ixis', types.Integer, primary_key=True),
	Column('ixi', types.Integer, ForeignKey('items.ixi')),
	Column('ixs', types.Integer, ForeignKey('sources.ixs')
))

item_clips_table = Table('item_clips', metadata,
	Column('ixic', types.Integer, primary_key=True),
	Column('ixi', types.Integer, ForeignKey('items.ixi')),
	Column('ixc', types.Integer, ForeignKey('clips.ixc')
))

item_heads_table = Table('item_heads', metadata,
	Column('ixih', types.Integer, primary_key=True),
	Column('ixi', types.Integer, ForeignKey('items.ixi')),
	Column('ixh', types.Integer, ForeignKey('heads.ixh')
))

metas_table = Table('metas', metadata,
	Column('ixm', types.Integer, primary_key=True),
	Column('id', types.String()),
	Column('name', types.String()),
	Column('content', types.String()),
	Column('scheme', types.String())
)

head_metas_table = Table('head_metas', metadata,
	Column('ixhm', types.Integer, primary_key=True),
	Column('ixh', types.Integer, ForeignKey('heads.ixh')),
	Column('ixm', types.Integer, ForeignKey('metas.ixm')
))

clip_metas_table = Table('clip_metas', metadata,
	Column('ixcm', types.Integer, primary_key=True),
	Column('ixc', types.Integer, ForeignKey('clips.ixc')),
	Column('ixm', types.Integer, ForeignKey('metas.ixm')
))

heads_table = Table('heads', metadata,
	Column('ixh', types.Integer, primary_key=True),
	Column('id', types.String()),
	Column('title', types.String())
)

clips_table = Table('clips', metadata,
	Column('ixc', types.Integer, primary_key=True ),
	Column('start_time', types.Float ),
	Column('end_time', types.Float ),
	Column('id', types.String()),
	Column('a_href', types.String()),
	Column('a_text', types.String()),
	Column('img_src', types.String()),
	Column('desc', types.String()),
)

rename_table = Table('rename', metadata,
	Column('ixr', types.Integer, primary_key=True),
	Column('oldname', types.String()),
	Column('newname', types.String())
)

channels_table = Table('channels', metadata,
	Column('ixch', types.Integer, primary_key=True ),
	Column('title', types.String()),
	Column('link', types.String()),
	Column('description', types.String()),
	Column('language', types.String()),
	Column('copyright', types.String()),
	Column('managingEditor', types.String()),
	Column('webMaster', types.String()),
	Column('image_path', types.String())
)

stories_table = Table('stories', metadata,
	Column('ixst', types.Integer, primary_key=True ),
	Column('id', types.String()),
	Column('title', types.String()),
	Column('pubdate', types.String()),
	Column('link', types.String()),
	Column('description', types.String()),
	Column('author', types.String()),
	Column('category', types.String())
)

source_info_table = Table('source_info', metadata,
	Column('ixs', types.Integer, primary_key=True ),
	Column('duration', types.String()),
	Column('bitrate', types.String()),
	Column('resolution', types.String()),
	Column('framerate', types.String()),
	Column('has_video', types.Integer),
	Column('has_audio', types.Integer),
	Column('audio', types.String())
)

class System(object):
    def __str__(self):
        return self.name

class Collection(object):
    def __str__(self):
        return self.name

class Item(object):
    def __str__(self):
        return self.name

class Collectionitems(object):
    def __str__(self):
        return self.name

class Playlist(object):
    def __str__(self):
        return self.name

class Playlistitems(object):
    def __str__(self):
        return self.name

class Source(object):
    def __str__(self):
        return self.name

class Itemsources(object):
    def __str__(self):
        return self.name

class Itemclips(object):
    def __str__(self):
        return self.name

class Itemheads(object):
    def __str__(self):
        return self.name

class Meta(object):
    def __str__(self):
        return self.name

class Headmetas(object):
    def __str__(self):
        return self.name

class Clipmetas(object):
    def __str__(self):
        return self.name

class Head(object):
    def __str__(self):
        return self.name

class Clip(object):
    def __str__(self):
        return self.name

class Rename(object):
    def __str__(self):
        return self.name

class Channel(object):
    def __str__(self):
        return self.name

class Story(object):
    def __str__(self):
        return self.name

class Sourceinfo(object):
    def __str__(self):
        return self.name

mapper(Collection, collections_table)
mapper(Collectionitems, collection_items_table)

mapper(Item, items_table, properties = {
        'my_collections' : relation(Collection,
                           secondary = collection_items_table)})

mapper(Playlist, playlists_table)

mapper(Playlistitems, playlist_items_table)

#mapper(Collection, collections_table)

mapper(Source, sources_table, properties = {
	'my_items' : relation(Item, secondary = item_sources_table)
	})
mapper(Sourceinfo, source_info_table)
mapper(Itemsources, item_sources_table)
mapper(Head, heads_table, properties = {
	'my_items' : relation(Item, secondary = item_heads_table)
	})

mapper(Itemheads, item_heads_table)
mapper(Meta, metas_table, properties = {
	'my_heads' : relation(Head, secondary = head_metas_table)
	})

mapper(Headmetas, head_metas_table)
mapper(Clip, clips_table, properties = {
	'my_items' : relation(Item, secondary = item_clips_table)
	})

mapper(Itemclips, item_clips_table)

def getallitems():
    """ get a list of all items in the database

        returns a list of dicts with keys "index" and "name"
    """
    item_q = Session.query(Item)
    i = item_q.all()
    items = []
    for n in i:
        item = {}
        item['index'] = n.ixi
        item['name'] = n.name
        items.append(item)

    return items

def addnewcollection(name, role, access):
    """ Adds a new collection to the database given a name and access info

    """
    newcollection = Collection()
    newcollection.name = name
    newcollection.role = role
    newcollection.access = access
    Session.save(newcollection)
    
    Session.commit()
    return

#def deletemeta(metaid):
#    meta_q = Session.query(Meta)
#    m = meta_q.filter_by(ixm=metaid).first()
#    Session.delete(m)
#    Session.commit()
#    return


def deletecollection(id):
    """ Deletes a collection - just the collection name and entry, not
        all of the items under it
    """
    coll_q = Session.query(Collection)
    c = coll_q.filter_by(ixk=id).first()
    Session.delete(c)
    Session.commit()
    return

def getcollections():
    """ gets a list of all collections in the database

        returns a list of dicts with keys "index" and "name"
    """
    coll_q = Session.query(Collection)
    c = coll_q.all()
#    c = coll_q.filter_by()
    collections=[]
    for n in c:
        collection = {}
        collection['index'] = n.ixk
        collection['name'] = n.name
        collection['role'] = n.role
        collection['access'] = n.access
        collections.append(collection)

    return collections

def getcollectionswithrole(authrole):
    """ Gets a list of collections with the stated role
        returns a list of dicts with keys "index" and "name"
    """
#    auth_q = Session.query(Authorisation)
#    a = auth_q.filter_by(role=authrole)
    collections=[]
    coll_q = Session.query(Collection)
    c = coll_q.filter_by(role=authrole)
    for n in c:
        collection = {}
        collection['index'] = n.ixk
        collection['name'] = n.name
        collection['role'] = n.role
        collection['access'] = n.access
        collections.append(collection)

    return collections



def getitemsincollection(id):
    """ Gets all items from a collection given a collection index value

        returns a list of dictionaries with keys "index" and "name"
    """
    coll_items_q = Session.query(Collectionitems)
    # get a list of ids 
    cilist = coll_items_q.filter_by(ixk=id)
    items = []
    for ci in cilist:
        item={}
        item_q = Session.query(Item)
        i = item_q.filter_by(ixi=ci.ixi).first()
        item['index'] = i.ixi
        item['name'] = i.name
        items.append(item)

    Session.commit()

    return items

def getplaylists():
    """ get all current playlists from database

        returns a list of dicts with keys "index" and "name"
    """
    play_q = Session.query(Playlist)
    p = play_q.all()
    playlists=[]
    for n in p:
        playlist={}
        playlist['index']=n.ixp
        playlist['name']=n.name
        playlists.append(playlist)

    return playlists

def addnewplaylist(name):
    """ Add a new named playlist to the database

    """
    newplaylist = Playlist()
    newplaylist.name = name
    Session.save(newplaylist)
    Session.commit()
    return

def addcliptoplaylist(list, clipid):
    """ Adds a clip to a playlist

    """
    # get the id for the list
    p_q = Session.query(Playlist)
    p = p_q.filter_by(name=list).first()
    # get the item id:
    ic_q = Session.query(Itemclips)
    ic = ic_q.filter_by(ixc=clipid).first()
    # add an entry to playlist_items
    pi = Playlistitems()
    pi.ixc = clipid
    pi.ixi = ic.ixi
    pi.ixp = p.ixp
    Session.save(pi)
    Session.commit()
    return

def additemtoplaylist(list, itemid):
    """ Adds an item to a playlist

    """
    p_q = Session.query(Playlist)
    p = p_q.filter_by(name=list).first()
    pi = Playlistitems()
    #pi.ixc = clipid
    pi.ixi = itemid
    pi.ixp = p.ixp

    Session.save(pi)
    Session.commit()
    return


def getclipsfromplaylist(listid,role):
    pi_q = Session.query(Playlistitems)
    pi = pi_q.filter_by(ixp=listid).all()
    #print pi
    c_q = Session.query(Clip)
    ci_q = Session.query(Collectionitems)
    coll_q = Session.query(Collection)
    clips = []
    for item in pi:
        if item.ixc:
            print item.ixc
            clip = {}
            c = c_q.filter_by(ixc=item.ixc).first()
            # Get the associated item and collection if user role is public
            if role=="public":
                ci = ci_q.filter_by(ixi=item.ixi).first()
                coll = coll_q.filter_by(ixk=ci.ixk).first()
        # if its role is admin, and the user role is public then skip it
                if coll.role =="admin":
                    continue
            clip['index'] = c.ixc
            clip['start'] = c.start_time
            clip['end'] = c.end_time
            clip['id'] = c.id
            clip['a_href'] = c.a_href
            clip['a_text'] = c.a_text
            clip['img_src'] = c.img_src
            clip['desc'] = c.desc
            clips.append(clip)
        
    return clips

def getitemsfromplaylist(listid,role):
    """ This gets items in the playlist that have no clip info

    """
    pi_q = Session.query(Playlistitems)
    ci_q = Session.query(Collectionitems)
    coll_q = Session.query(Collection)
    pi = pi_q.filter_by(ixp=listid).all()
    i_q = Session.query(Item)
    items = []
    for n in pi:
        #print item.ixc
        if (not n.ixc):
            item = {}
            i = i_q.filter_by(ixi=n.ixi).first()
            # Get the associated item and collection if user role is public
            if role=="public":
                ci = ci_q.filter_by(ixi=n.ixi).first()
                coll = coll_q.filter_by(ixk=ci.ixk).first()
        # if its role is admin, and the user role is public then skip it
                if coll.role =="admin":
                    continue
            item['index'] = i.ixi
            item['name'] = i.name
            item['cmmlfile'] = i.cmmlfile
            items.append(item)

    return items

def deleteplaylist(listname):
    """ deletes a playlist and items in the playlist_items table
        given the playlist name
    """
    p_q = Session.query(Playlist)
    p = p_q.filter_by(name=listname).first()
    # then get all items in the playlist_items table
    pi_q = Session.query(Playlistitems)
    pi = pi_q.filter_by(ixp=p.ixp).all()
    for item in pi:
        Session.delete(item)

    Session.delete(p)
    Session.commit()

def addsource(itemname, source, cmmlfile):
    new_item = Item()
    new_source = Source()
    new_item.name = itemname
    new_item.cmmlfile = cmmlfile
    new_source.path = source
    new_source.my_items.append(new_item) 
	
    Session.save(new_item)
    Session.save(new_source)
    Session.commit()
    return new_item.ixi

def additemtocollection(itemid, collection):
    new_coll_item = Collectionitems()
    coll_q = Session.query(Collection)
    #c = coll_q.filter_by(name=collection)
    c = coll_q.filter_by(name=collection).first()
    new_coll_item.ixk = c.ixk
    new_coll_item.ixi = itemid
    Session.save(new_coll_item)
    Session.commit()
    return
    
def edititemname(itemid, text):
    """ Edits the name of an item


    """
    item_q = Session.query(Item)
    #i = item_q.filter_by(ixi=itemid)
    i = item_q.filter_by(ixi=itemid).first()
    i.name = text
    Session.commit()

    return
     
def getitemname(id):
    item_q = Session.query(Item)
    #i = item_q.filter_by(ixi=id)
    i = item_q.filter_by(ixi=id).first()
    name = i.name
    return name

#def getitemforid(id):
#    # FIXME Not returning a dict - returning a database object at this stage
#    item_q = Session.query(Item)
#    i = item_q.filter_by(ixi=id).first()
#    return i

def getitemforclip(clipid):
    # FIXME Not returning a dict - returning a database object at this stage
    ic_q = Session.query(Itemclips)
    ic = ic_q.filter_by(ixc=clipid).first()
    i_q = Session.query(Item)
    i = i_q.filter_by(ixi=ic.ixi).first()

    item = {}
    item['ixi'] = i.ixi
    item['name'] = i.name
    item['cmmlfile'] = i.cmmlfile

    return i


#def getclip(clipid):
#    c_q = Session.query(Clip)
#    c = c_q.filter_by(ixc=clipid).first()
#    return c

#def getheadmeta(id):
#    meta_q = Session.query(Meta)
#    m = meta_q.filter_by(ixm=id).first()
#    return m

#def getitemforheadmeta(metaid):
#    #first get the head
#    hm_q = Session.query(Headmetas)
#    h = hm_q.filter_by(ixm=metaid).first()
#    ih_q = Session.query(Itemheads)
#    ih = ih_q.filter_by(ixh=h.ixh).first()
#    i_q = Session.query(Item)
#    i = i_q.filter_by(ixi=ih.ixi).first()
#    return i


def getitemcmmlfile(id):
    item_q = Session.query(Item)
    i = item_q.filter_by(ixi=id).first()
    cmmlfile = i.cmmlfile
    return cmmlfile

def getsourcesforitem(id):
    # get the results from item_sources for given item id
    item_sources_q = Session.query(Itemsources)
    s = item_sources_q.filter_by(ixi=id).first()
    sid = s.ixs 
    return sid 

def getsourcepath(id):
    """ get the source path given a sourceid

        Returns the source path
     """
    source_q = Session.query(Source)
    s = source_q.filter_by(ixs=id).first()
    path = s.path
    return path

def addhead(info):
    # get the item this refers to:
    item_q = Session.query(Item)
    i = item_q.filter_by(ixi=info['itemid']).first()
    new_head = Head()    
    new_head.id = info['id']
    new_head.title = info['title']
    new_head.my_items.append(i)
    Session.save(new_head)
    Session.commit() 
    return new_head.ixh

def edithead(info):
    head_q = Session.query(Head)
    itemhead_q = Session.query(Itemheads)
    ih = itemhead_q.filter_by(ixi=info['itemid']).first()
    h = head_q.filter_by(ixh=ih.ixh).first()
    h.id = info['id']
    h.title = info['title']
    Session.commit() 
    return   

def getclipinfoforitem(id):
    item_clip_q = Session.query(Itemclips)
    iclist = item_clip_q.filter_by(ixi=id).all()
    cliplist = []
    clip_q = Session.query(Clip)

    for ic in iclist:
        # get individual meta info
        clipinfo = {}
        c = clip_q.filter_by(ixc=ic.ixc).first()
        clipinfo['ixc'] = c.ixc
        clipinfo['start_time'] = c.start_time
        clipinfo['end_time'] = c.end_time
        clipinfo['id'] = c.id
        clipinfo['a_href'] = c.a_href
        clipinfo['a_text'] = c.a_text
        clipinfo['img_src'] = c.img_src
        clipinfo['desc'] = c.desc
        cliplist.append(clipinfo)
    # great way to sort a list of dictionaries:
    sortedcliplist = sorted(cliplist, key=itemgetter('start_time'))
    Session.commit()
    return sortedcliplist

def getheadinfoforitem(id):
    headdict = {}
    head_q = Session.query(Head)
    itemhead_q = Session.query(Itemheads)
    ih = itemhead_q.filter_by(ixi=id).first()
    h = head_q.filter_by(ixh=ih.ixh).first()
    headdict['index'] = h.ixh
    headdict['id'] = h.id
    headdict['title'] = h.title
    return headdict

def getmetasforhead(headid):
    head_meta_q = Session.query(Headmetas)
    hmlist = head_meta_q.filter_by(ixh=headid).all()
    metalist = []
    meta_q = Session.query(Meta)
    for hm in hmlist:
        metainfo = {}
        m = meta_q.filter_by(ixm=hm.ixm).first()
        metalist.append(m)

    Session.commit()
    return metalist

def getmetainfoforhead(id):
    """ Get meta info for an item's head

        Returns a list of dicts with keys:
            index, id, name, content, scheme
    """
    head_meta_q = Session.query(Headmetas)
    hmlist = head_meta_q.filter_by(ixh=id).all()
    metas = []
    meta_q = Session.query(Meta)
    for hm in hmlist:
        # get individual meta info
        meta= {}
        m = meta_q.filter_by(ixm=hm.ixm).first()
        meta['index'] = m.ixm
        meta['id'] = m.id
        meta['name'] = m.name
        meta['content'] = m.content
        meta['scheme'] = m.scheme
        metas.append(meta)

    Session.commit()
    return metas

def addmeta(info):
    """ Given a dictionary of meta info, adds a meta item to database

        returns the newly created meta item
    """ 
    head_q = Session.query(Head)
    h  = head_q.filter_by(ixh=info['headindex']).first()
    new_meta = Meta() 
    new_meta.id = info['id']
    new_meta.name = info['name']
    new_meta.content = info['content']
    new_meta.scheme = info['scheme']
    new_meta.my_heads.append(h)
    Session.save(new_meta)
    Session.commit()
    return new_meta.ixm

def syncmetaid(metaid):
    """ Given the meta primary key, it sets id to the same value

    """

    meta_q = Session.query(Meta)
    m = meta_q.filter_by(ixm=metaid).first()
    m.id = metaid
    Session.commit()
    return

def editmeta(metaid, field, text):
    """ Edits a meta tag in the database

        metaid
        field - the field (or database column) to change
        text - the text that replaces the existing entry

        returns nothing yet... 
    """
    meta_q = Session.query(Meta)
    m = meta_q.filter_by(ixm=metaid).first()
    if field=="name":
        m.name = text
    elif field=="content":
        m.content = text
    Session.commit()
    return

def addclip(info):
    item_q = Session.query(Item)
    i = item_q.filter_by(ixi=info['itemid']).first()
    new_clip = Clip()
    new_clip.start_time = info['starttime']
    if info['endtime'] == "":
        new_clip.end_time = 0
    else:
        new_clip.end_time = info['endtime']
    new_clip.id = info['id']
    new_clip.a_href = info['a_href']
    new_clip.a_text = info['a_text']
    new_clip.img_src = info['img_src']
    new_clip.desc = info['desc']
    new_clip.my_items.append(i)
    Session.save(new_clip)
    Session.commit()
    return

def deleteclip(id):
    clip_q = Session.query(Clip)
    c = clip_q.filter_by(ixc=id).first()
    Session.delete(c)
    Session.commit()
    return
    
def deleteitem(id):
    item_q = Session.query(Item)
    i = item_q.filter_by(ixi=id).first()
    Session.delete(i)

    # remove head info
    ih_q = Session.query(Itemheads)
    ih = ih_q.filter_by(ixi=id).first()

    # remove meta tags from this head
    hm_q = Session.query(Headmetas)
    hm = hm_q.filter_by(ixh=ih.ixh)
    m_q = Session.query(Meta)
    for headmeta in hm:
        m = m_q.filter_by(ixm=headmeta.ixm).first()
#        Session.delete(headmeta)
        Session.delete(m)

    # Remove all clips
    ic_q = Session.query(Itemclips)
    c_q = Session.query(Clip)
    ic = ic_q.filter_by(ixi=id)
    for itemclip in ic:
        c = c_q.filter_by(ixc=itemclip.ixc).first()
        Session.delete(c)
    
    # remove the head entry
    h_q = Session.query(Head)
    h = h_q.filter_by(ixh=ih.ixh).first()
    Session.delete(h)

    # remove the source information as well
    is_q = Session.query(Itemsources)
    i_s = is_q.filter_by(ixi=id).first()
    s_q = Session.query(Source)
    s = s_q.filter_by(ixs=i_s.ixs).first()
    #Session.delete(i_s)
    Session.delete(s)

    Session.commit()
    return


def deletemeta(metaid):
    meta_q = Session.query(Meta)
    m = meta_q.filter_by(ixm=metaid).first()
    Session.delete(m)
    Session.commit()
    return

def searchforclips(searchterms, role):
    # search for clips within the database, and return a list of dictionaries
    # with keys "clipindex", "itemindex", "itemname", "clipdesc", "clipid"
    
    searchlist = string.split(searchterms, " ")
    session = Session()
    clip_q = session.query(Clip)
    for term in searchlist:
        searchstring = "%" + term + "%"
        clip_q = clip_q.filter(Clip.desc.like(searchstring))

#   Notes on more complex searching...
#    (clips_table.c.desc.like(a) AND clips_table.c.desc.like(b) AND...

    clips = []
    ic_q = Session.query(Itemclips)
    it_q = Session.query(Item)
    ci_q = Session.query(Collectionitems)
    c_q = Session.query(Collection)
    for i in clip_q:
        clip = {}
        ic = ic_q.filter_by(ixc=i.ixc).first()
        item = it_q.filter_by(ixi=ic.ixi).first() 
        # Get the associated collection only if user role is public
        if role=="public":
            ci = ci_q.filter_by(ixi=ic.ixi).first()
            coll = c_q.filter_by(ixk=ci.ixk).first()
        # if its role is admin, and the user role is public then skip it
            if coll.role =="admin":
                continue
        clip['itemindex'] =  item.ixi
        clip['itemname'] = item.name
        clip['index'] = i.ixc
        clip['id'] = i.id
        clip['desc'] = i.desc
        clips.append(clip)
        #clipnames.append(clip.ixc)

    return clips


def searchforitems(searchterms, role):
    searchlist = string.split(searchterms, " ")
    session = Session()
    item_q = session.query(Item)
    ci_q = Session.query(Collectionitems)
    c_q = Session.query(Collection)
    for term in searchlist:
        searchstring = "%" + term + "%"
        item_q = item_q.filter(Item.name.like(searchstring))

    items = []
    for n in item_q:
        item = {}
        # Get the associated collection only if user role is public
        if role=="public":
            ci = ci_q.filter_by(ixi=n.ixi).first()
            coll = c_q.filter_by(ixk=ci.ixk).first()
        # if its role is admin, and the user role is public then skip it
            if coll.role =="admin":
                continue
        item['index'] = n.ixi
        item['name'] = n.name
        item['cmmlfile'] = n.cmmlfile
        items.append(item)

    return items


def searchformetas(searchterms, role):
    """ searches in the meta name and content fields of the database

        returns a list of dictionaries with keys:
            index, name, content, itemindex, itemname
    """
    # split search string at whitespace:
    searchlist = string.split(searchterms, " ")
#    searchstring = "%" + searchterm + "%"
    session = Session()
#    meta_q = session.query(Meta)
#    statement = metas_table.select()
    meta_q = session.query(Meta)
    for term in searchlist:
        searchstring = "%" + term + "%"
        #meta_q = meta_q.filter(Meta.name.like(searchstring))
        meta_q = meta_q.filter(or_(Meta.name.like(searchstring), Meta.content.like(searchstring)))

    hm_q = Session.query(Headmetas)
    ih_q = Session.query(Itemheads)
    i_q = Session.query(Item)
    ci_q = Session.query(Collectionitems)
    c_q = Session.query(Collection)
    metas = []
    for n in meta_q:
        meta = {}
        hm = hm_q.filter_by(ixm=n.ixm).first()
        ih = ih_q.filter_by(ixh=hm.ixh).first()
        i = i_q.filter_by(ixi=ih.ixi).first()
        # Get the associated collection only if user role is public
        if role=="public":
            ci = ci_q.filter_by(ixi=i.ixi).first()
            coll = c_q.filter_by(ixk=ci.ixk).first()
        # if its role is admin, and the user role is public then skip it
            if coll.role =="admin":
                continue
        meta['index'] = n.ixm
        meta['name'] = n.name
        meta['content'] = n.content
        meta['itemindex'] = i.ixi
        meta['itemname'] = i.name
        metas.append(meta)

    return metas
